package com.sushe.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BedDao {
    private static final String url="jdbc:mysql://localhost:3306/sushe";
    private static final String username="root";
    private static final String password="123456";

    //添加房间时添加房间中四个床位
    public  void addBed(Integer bedNumber,Integer roomId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="insert into bed (bed_number,room_id) values(?,?)";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,bedNumber);
        stmt.setInt(2,roomId);
        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }


    public  void deleteStudentIdByStudentId(Integer studentId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="update bed set student_id=null where student_id=?";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,studentId);
        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }


    public  Integer getRoomIdByStudentId(Integer studentId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select room_id from bed where student_id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,studentId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer roomId = rs.getInt("room_id");
            return roomId;
        }
        return null;
    }

    public  String getBuildingNameByRoomId(Integer roomId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select b.name\n" +
                "from room r join dorm_building b on r.building_id = b.id\n" +
                "where r.id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            String buildingName=rs.getString("name");
            return buildingName;
        }
        return null;
    }


    public  List<Integer> StudentIn(Integer roomId) throws ClassNotFoundException, SQLException {
        List<Integer> list=new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select student_id from bed where room_id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer studentId = rs.getInt("student_id");
            list.add(studentId);
        }
        return list;
    }


    public  void delete(Integer roomId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="delete from bed where room_id=?";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);

        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }
}
